package com.cyanite.playlv2.widget;

import com.cyanite.playlv2.R;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseAdapter   {
	public static final String KEY_ID = "id";
	public static final String KEY_CITY_ID = "city_id";
	public static final String KEY_COUNTRY = "country";
	public static final String KEY_STATE = "state";
	public static final String KEY_CITY = "city";
	public static final String KEY_SCENERY	 = "scenery";
	public static final String KEY_LATITUDE	= "latitude";
	public static final String KEY_LONGTITUED	 = "longitude";
	private static final String	DB_NAME	= "trip.db";
	private static final String	TABLE_CITY	 = "citys";
	private static final String	TABLE_SCENERY = "scenery";
	// private static final String TABLE_MAINCATEGORY = "MainCategory";
	// private static final String TABLE_SECONDCATEGORY = "SecondCategory";
	// private static final String TABLE_THIRDCATEGORY = "ThirdCategory";
	private static final int DB_VERSION = 1;
	private Context	mContext = null;

	/*private static final String DB_CREATE_CITY = "CREATE TABLE " + TABLE_CITY + "(" +KEY_ID+" INTERGER PRIMARY KEY,"
																			+KEY_COUNTRY+" TEXT,"
																			+KEY_STATE+" TEXT,"
																			+KEY_CITY+" TEXT,"
																			+KEY_LATITUDE+" TEXT,"
																			+KEY_LONGTITUED+" TEXT)";
	private static final String DB_CREATE_SCENERY = "CREATE TABLE " + TABLE_SCENERY + "(" +KEY_ID+" INTERGER PRIMARY KEY,"
																			+KEY_CITY_ID+" INTERGER,"
																			+KEY_SCENERY+" TEXT,"
																			+KEY_LATITUDE+" TEXT,"
																			+KEY_LONGTITUED+" TEXT)";*/

	public static final String SELECT = "select * from ";
	private SQLiteDatabase mSQLiteDatabase	= null;
	private DatabaseHelper	 mDatabaseHelper	= null;

	private static class DatabaseHelper extends SQLiteOpenHelper   {
		DatabaseHelper(Context context)   {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db)	{
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)  {
			db.execSQL("DROP TABLE IF EXISTS notes");
			onCreate(db);
		}
	}

	public void initDataBase()  {
        File saveFile=new File("/data/data/com.cyanite.playlv2/databases/trip.db");
        InputStream stream = mContext.getResources().openRawResource(R.raw.trip);

        try {
            byte[] buffer = null;
            int dbFileFromApkLength = stream.available();
            int dbFileFromlocalLength = (int) saveFile.length();
            if(dbFileFromlocalLength < dbFileFromApkLength){
                buffer = new byte[dbFileFromApkLength];
                stream.read(buffer);
                FileOutputStream outStream = new FileOutputStream(saveFile);
                outStream.write(buffer);
                outStream.flush();
                outStream.close();
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

	public DataBaseAdapter(Context context)   {
		mContext = context;
	}

	public void open() throws SQLException {
		mDatabaseHelper = new DatabaseHelper(mContext);
		mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
	}

	public void close()    	{
		mDatabaseHelper.close();
	}

	public boolean addCityData(int id,String Country,String state,String city,String latitude,String longitude){
	    ContentValues cv = new ContentValues();
	    cv.put(KEY_ID,id);
        cv.put(KEY_COUNTRY,Country);
        cv.put(KEY_STATE,state);
        cv.put(KEY_CITY,city);
        cv.put(KEY_LATITUDE,latitude);
        cv.put(KEY_LONGTITUED,longitude);
        return (mSQLiteDatabase.insert(TABLE_CITY, null, cv)!=-1);

	}

	public boolean addSceneryData(int id,int city_id,String scenery,String latitude,String longitude){
	    ContentValues cv = new ContentValues();
        cv.put(KEY_ID,id);
        cv.put(KEY_CITY_ID,city_id);
        cv.put(KEY_SCENERY,scenery);
        cv.put(KEY_LATITUDE,latitude);
        cv.put(KEY_LONGTITUED,longitude);
        return (mSQLiteDatabase.insert(TABLE_SCENERY, null, cv)!=-1);
	}

	private void getList(String table, String columName, List<String> list)  {
        if ( list != null )  {
            Cursor cur = mSQLiteDatabase.rawQuery(SELECT + table, null);
            if ( cur != null )  {
                if ( cur.moveToFirst() )   {
                    do{
                        list.add(cur.getString(cur.getColumnIndex(columName)));
                    }while(cur.moveToNext());
                }
            }
            cur.close();
        }
    }

	public void getPlacesList(List<String> list)   {
		getList( TABLE_SCENERY, KEY_SCENERY, list );
	}

	public void getCityList(List<String> list) {
		getList( TABLE_CITY, KEY_CITY, list );
	}

	/*
	private void getCategories(Group<VenueCategory> categories)   {
	    if ( categories != null )  {
            Cursor cur = mSQLiteDatabase.rawQuery(SELECT+TABLE_MAINCATEGORY, null);
            if ( cur != null )  {
                if ( cur.moveToFirst() )   {
                    do {
                       VenueCategory category = new VenueCategory();
                       category.setName(cur.getString(cur.getColumnIndex("name")));

                       //int isId = cur.getInt(cur.getColumnIndex("child"));
                       //int id = cur.getInt(cur.getColumnIndex("id"));
                       //String strSql = SELECT + TABLE_SECONDCATEGORY + String.format(" where main_id=%d", id);

                       //if( isId == 1 )  {
                       //    getChildCategories(category, strSql);
                       //}

                       categories.add(category);

                    } while(cur.moveToNext());
                }
            }
            cur.close();
        }
	}
	*/

	/*
	private void getChildCategories(VenueCategory root, String situation)    {
        if ( root != null )  {
            Cursor cur = mSQLiteDatabase.rawQuery(situation, null);
            if ( cur != null )  {
                Group<VenueCategory> child = root.getChildCategories();
                if ( cur.moveToFirst() )   {
                    do{
                       VenueCategory category = new VenueCategory();
                       category.setName(cur.getString(cur.getColumnIndex("name")));

                       int isId = cur.getInt(cur.getColumnIndex("child"));
                       int id = cur.getInt(cur.getColumnIndex("id"));
                       String sql = "";
                       switch( isId ){
                          case 1:
                              sql = SELECT + TABLE_THIRDCATEGORY + String.format(" where sec_id=%d", id);
                              getChildCategories(category, sql);
                              break;
                          case 2:
                              sql = SELECT + TABLE_THIRDCATEGORY + String.format(" where sec_id=%d", 0);
                              getChildCategories(category, sql);
                              break;
                       }
                      child.add(category);
                    } while(cur.moveToNext());
                }
            }
            cur.close();
        }
    }
    */
}

